﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data;
using System.Data;
using MySql.Data.MySqlClient;

namespace myShop
{
    //mysql for net 下载网站 https://downloads.mysql.com/archives/c-net/
    public class MysqlConnector
    {
        LogFile Log = new LogFile();

        public MysqlConnector() { }
        

        #region  建立MySql数据库连接
        /// <summary>
        /// 建立数据库连接.
        /// </summary>
        /// <returns>返回MySqlConnection对象</returns>
        private MySqlConnection GetMysqlConnection()
        {
            MySqlConnection myCon=null;
            string M_str_sqlcon = string.Format("server={0};uid={1};pwd={2};database={3};port={4};Charset=utf8;", Global.m_server, Global.m_userid, Global.m_password, Global.m_database, Global.m_port);
            try
            {
                myCon = new MySqlConnection(M_str_sqlcon);
            }
            catch (Exception ex)
            {
                Console.WriteLine("数据库连接失败:" + ex.Message);
                Log.doWriteLog(1, "mysql:" + M_str_sqlcon);
                Log.doWriteLog(1, "mysql连接失败:" + ex.Message);
            }
            return myCon;
        }
        #endregion

        #region  执行MySqlCommand命令
        /// <summary>
        /// 执行MySqlCommand
        /// </summary>
        /// <param name="M_str_sqlstr">SQL语句</param>
        public void ExeUpdate(string M_str_sqlstr)
        {
            try
            {
                MySqlConnection mysqlcon = this.GetMysqlConnection();
                mysqlcon.Open();
                MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
                mysqlcom.ExecuteNonQuery();
                mysqlcom.Dispose();
                mysqlcon.Close();
                mysqlcon.Dispose();
            }
            catch { }
        }
        #endregion

        #region  创建MySqlDataReader对象
        /// <summary>
        /// 创建一个MySqlDataReader对象
        /// </summary>
        /// <param name="M_str_sqlstr">SQL语句</param>
        /// <returns>返回MySqlDataReader对象</returns>
        /// https://www.cnblogs.com/zwj1276952588/p/10499789.html
        //public MySqlDataReader ExeQuery(string M_str_sqlstr)
        public int ExeQuery(string M_str_sqlstr)
        {
            Console.WriteLine(M_str_sqlstr);
            int count = 0;
            try
            {
                MySqlConnection mysqlcon = this.GetMysqlConnection();
                MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
                mysqlcon.Open();
                Object result = mysqlcom.ExecuteScalar();//执行查询，并返回查询结果集中第一行的第一列。所有其他的列和行将被忽略。select语句无记录返回时，ExecuteScalar()返回NULL值
                if (result != null)
                {
                    count = int.Parse(result.ToString());
                }
            }
            catch{ }

            //MySqlDataReader mysqlread = mysqlcom.ExecuteReader();
            return count;
        }


        public DataTable query(string sql)
        {
            DataTable dt = null;
            try
            {
                MySqlConnection mysqlcon = this.GetMysqlConnection();

                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = mysqlcon;

                dt = new DataTable();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(dt);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return dt;
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// https://www.cnblogs.com/zwj1276952588/p/10499789.html
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>     
        /// https://blog.csdn.net/qq_31493411/article/details/105293279
        public int ExecuteSqlTran(List<String> SQLStringList)
        {
            MySqlConnection mysqlcon = this.GetMysqlConnection();
            mysqlcon.Open();//必须打开通道之后才能开始事务

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = mysqlcon;
            MySqlTransaction tx = mysqlcon.BeginTransaction();
            cmd.Transaction = tx;
            string strsql = string.Empty; 
            try
            {
                int count = 0;
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    strsql = SQLStringList[n];
                    if (Global.i_debug.Equals("1"))
                    {
                        Log.doWriteLog(1, strsql);
                    }
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        cmd.CommandTimeout = 3600;
                        count = cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();

                mysqlcon.Close();
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Log.doWriteLog(1, strsql);
                Log.doWriteLog(1, ex.Message);
                tx.Rollback();
                mysqlcon.Close();
                return 0;
            }
            
        }

        #endregion
    }
    //原文链接：https://blog.csdn.net/lgj123xj/article/details/70851519
}
